<?php
class mondat_mdl extends CI_Model
{


	
	public function get_all($limit, $start)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
facture.montant_fact,
facture.id_facture,
facture.id_mandat_fk,
facture.id_fournisseur_fk,
facture.image,
facture.numero,
mandats.numero AS num_mondat,
mandats.montant,
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
mandats.numero_cheque,
mandats.date
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
LEFT JOIN bcs ON bcs.id_facture_fk = facture.id_facture
LEFT JOIN conventions ON conventions.id_facture_fk = facture.id_facture
WHERE
mandats.annee = $year
	LIMIT  $start ,$limit
 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;
		}
		
		public function get_all_recherche()
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
mandats.annee = $year
 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;
		}
		
		public function get_recherche_per_art($art)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv,
mandats.date,
mandats.montant,
mandats.numero_cheque AS cheque,
mandats.numero
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
conventions.id_article_fk = $art OR
bcs.id_article_fk = $art
and
mandats.annee = $year

 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;
		}
	
		public function get_recherche_per_nom($nom)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv,
mandats.date,
mandats.montant,
mandats.numero_cheque AS cheque,
mandats.numero
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE foun_bc.nom LIKE '%$nom%' or 
 fournisseurs.nom LIKE '%$nom%'
and
mandats.annee = $year
 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;
		}
		
		public function get_all2($limit, $start, $xxx)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv,
mandats.date,
mandats.montant,
mandats.numero_cheque AS cheque,
mandats.numero
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
conventions.id_article_fk = $xxx OR
bcs.id_article_fk = $xxx
and
mandats.annee = $year
	LIMIT  $start ,$limit
 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;

	
	}
	
	 public function record_count($articl_id) {
	  
	  $this->load->library('session');
$year=$this->session->userdata('annee');
	
      $q = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv,
mandats.date,
mandats.montant,
mandats.numero_cheque AS cheque,
mandats.numero
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
conventions.id_article_fk = $articl_id OR
bcs.id_article_fk = $articl_id and
mandats.annee = $year
");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count_all() {
	  	
$this->load->library('session');
$year=$this->session->userdata('annee');

	$q = $this->db->query("SELECT
mandats.id_mandat,
mandats.numero_cheque AS cheque,
mandats.date,
mandats.montant
FROM
mandats
WHERE
mandats.annee = $year
");

$cont=$q->num_rows();
return $cont;

    }

 public function record_count_recherche($nom) {
	  	
      $q = $this->db->query("SELECT
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
foun_bc.nom AS nom_fourn_bc,
fournisseurs.nom AS nom_four_conv
FROM
mandats
LEFT JOIN bcs ON bcs.id_mondat_fk = mandats.id_mandat
LEFT JOIN conventions ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN fournisseurs AS foun_bc ON bcs.id_fournisseur_fk = foun_bc.id_fournisseur
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
foun_bc.nom LIKE '$nom' OR
fournisseurs.nom LIKE '$nom'

");

$cont=$q->num_rows();
return $cont;

    }


public function get_details($id){
	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
facture.montant_fact,
facture.id_facture,
facture.id_mandat_fk,
facture.id_fournisseur_fk,
facture.image,
facture.numero,
mandats.numero AS num_mondat,
mandats.montant,
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion,
mandats.numero_cheque,
mandats.date,
bcs.numero AS numero_bc,
conventions.numero AS numero_conv,
mandats.visa_cf,
mandats.date_visa_cf,
mandats.id_methode_pay_fk,
mandats.annee,
mandats.visa_compta,
mandats.date_visa_compta,
mandats.page_compta,
mandats.ligne_compt,
articles.nom,
articles.num_article
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
LEFT JOIN bcs ON bcs.id_facture_fk = facture.id_facture
LEFT JOIN conventions ON conventions.id_facture_fk = facture.id_facture
LEFT JOIN articles ON bcs.id_article_fk = articles.id_article OR conventions.id_article_fk = articles.id_article
WHERE
mandats.id_mandat = $id

 ");

  if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        else return false;
		}

     public function add_visa_cf($id,$visa_cf,$date){
	$query = $this->db->query("UPDATE `mandats` SET `visa_cf`='$visa_cf', `date_visa_cf`='$date' WHERE (`id_mandat`='$id')

 ");

		}

      public function add_visa_comptable($id,$visa_compt,$date,$page,$ligne,$cheque){
	$query = $this->db->query("UPDATE `mandats` SET `visa_compta`='$visa_compt', `date_visa_compta`='$date', `page_compta`='$page', `ligne_compt`='$ligne', `cheque`='$cheque' WHERE (`id_mandat`='$id')
 ");

		}

        	public function supprime_mondat($id) {
	  	
      $q = $this->db->query("DELETE FROM `mandats` WHERE (`id_mandat`='$id')
");

    }
	
	 public function update_mondat($id,$facture,$montant,$cheque,$bc)
	{

	
	$query =$this->db->query("
	UPDATE `mandats` SET `numero_cheque`='$cheque', `id_bc_fk`='$bc', `montant`='$montant' WHERE (`id_mandat`='$id')
 ");
	}
	
	 public function ajout_mondat($facture,$montant,$cheque,$bc,$date)
	{

	
	$query =$this->db->query("
	INSERT INTO `mandats` (`numero_cheque`, `id_bc_fk`,  `montant`,  `date`) VALUES ('$cheque', '$bc', '$montant','$date') ");
	}

     public function ajout_new_mondat($num_cheque,$montant,$date,$numero_mondat)
	{

		$this->load->library('session');
$year=$this->session->userdata('annee');
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	INSERT INTO `mandats` (`numero_cheque`, `id_methode_pay_fk`, `date`, `montant`, `numero`, `annee`) VALUES ('$num_cheque', '1', '$date', '$montant', '$numero_mondat','$year')
 ");
 
  $query2 = $this->db->query("
	SELECT mandats.id_mandat
FROM mandats
WHERE
mandats.numero = $numero_mondat AND
mandats.annee = $year
ORDER BY
mandats.id_mandat DESC
LIMIT 0, 1

 ");
 
 return $query2->result();
	}

     public function ajout_new_mondat2($montant,$date,$numero_mondat)
	{

		$this->load->library('session');
$year=$this->session->userdata('annee');
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	INSERT INTO `mandats` (`id_methode_pay_fk`, `date`, `montant`, `numero`, `annee`) VALUES ( '1', '$date', '$montant', '$numero_mondat','$year')
 ");
 
  $query2 = $this->db->query("
	SELECT mandats.id_mandat
FROM mandats
WHERE
mandats.numero = $numero_mondat AND
mandats.annee = $year
ORDER BY
mandats.id_mandat DESC
LIMIT 0, 1

 ");
 
 return $query2->result();
	}
	
	
     public function get_num_mondat()
	{

	$this->load->library('session');
$year=$this->session->userdata('annee');

	$query =$this->db->query("
	SELECT mandats.id_mandat,mandats.numero
FROM mandats
WHERE
mandats.annee = $year
ORDER BY
mandats.numero DESC
LIMIT 0, 1 ");
return $query->result();
	}
}

?>